﻿<cfcomponent 
			displayname="onlineclearance" 
			hint="Functions for exit interview are here" 
			output="false"
>

<cffunction 
				name="submitclearanceform"
				output="false"
				returnformat="plain" 
				returntype="String"
	>
	<cfargument 
			name="processkey"
	>
	
<cfquery name="Inserttoapp" datasource="IBOSE_GLOBAL">

	SELECT PERSONNELIDNO
      FROM EGINCLRMAIN
     WHERE PERSONNELIDNO = '#form.personnelid#'
     
</cfquery>

<cfif Inserttoapp.Recordcount GT 0 AND Inserttoapp.Recordcount NEQ "" >

	<cfset controlnumber = 'recordalreadyexist' />

<cfelse>
	
    <cfset todaydate = DateFormat(Now(), 'yyyy-mm-dd') />
    <cfset guuiidd   = right(CreateUUID(), 10) />
	<cfset controlnumber = guuiidd >
    
    
    <cfquery name="getGUID" datasource="#form.thecompany#" maxrows="1">

        SELECT GUID
          FROM CMFPA
         WHERE PERSONNELIDNO = '#form.personnelid#'
         ;
         
    </cfquery>
   
   
	<cfquery name="Inserttoapp" datasource="IBOSE_GLOBAL">

		INSERT INTO EGINCLRMAIN( ID,
                                 PERSONNELIDNO, 
        						 LASTNAME, 
                                 FIRSTNAME, 
                                 IMMEDSUPERIOR, 
                                 POSITIONTITLE, 
                                 DATEHIRED,
                                 DEPARTMENT,
                                 BIRTHDATE,
                                 EMAILADD,
                                 CONTACTNUM,
                                 CAUSEOFSEP,
                                 DATERESIGNEFFECT,
                                 MIDDLENAME,
                                 EXT,
                                 LEVEL,
                                 COMPANY,
                                 COSTCENTERCODE,
                                 BRANDORGROUP,
                                 DIVISION,
                                 SECTION,
                                 GENDER,
                                 AGE,
                                 CIVILSTATUS,
                                 ADDRESS,
                                 TENUREPERYEAR,
                                 TENUREPERMONTH,
                                 EMPLOYMENTSTATUS,
                                 DEPTHEAD,
                                 DATERECEIVEDRL,
                                 DATEACCOMPLISHED,
                                 GUID
                                 
                                 )
                         VALUES ( 
                         		 <cfqueryparam cfsqltype="cf_sql_varchar" value="#guuiidd#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.personnelid#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.lastname#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.firstname#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.immediatesuperior#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.position#">,
                                 <cfqueryparam cfsqltype="cf_sql_date" value="#form.datehired#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.departmentorstore#">,
                                 <cfqueryparam cfsqltype="cf_sql_date" value="#form.birthdate#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.email#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.mobilenumber#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.causeofseparation#">,
                                 <cfqueryparam cfsqltype="cf_sql_date" value="#form.effectivedater#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.middlename#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.ext#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.level#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.thecompany#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.costcenter#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.brandorgroup#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.division#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.section#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.gender#">,
                                 <cfqueryparam cfsqltype="cf_sql_integer" value="#form.age#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.civilstatus#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.address#">,
                                 <cfqueryparam cfsqltype="cf_sql_integer" value="#form.tenureperyear#">,
                                 <cfqueryparam cfsqltype="cf_sql_integer" value="#form.tenurepermonth#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.employmentstatus#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.departmenthead#">,
                                 <cfqueryparam cfsqltype="cf_sql_date" value="#form.datereceivedrletter#">,
                                 <cfqueryparam cfsqltype="cf_sql_date" value="#form.dateaccomplished#">,
                                 <cfqueryparam cfsqltype="cf_sql_varchar" value="#getGUID.GUID#">
                                 
                         		);
                                 
                                   
	</cfquery>
 
</cfif>   
			
			<cfreturn  
						controlnumber
			>
	
</cffunction>
	
<cffunction 
			name="loadclearanceformform"
			returnformat="JSON" 
			returntype="String" 
			output="true"
>

<cfargument name="processkey" >
<cfargument name="employeeaidee" >
<cfargument name="companydsn" >
	
<cftry>
	
	<cfset processkey = processkey>	 
	<cfset thepid = employeeaidee>  
	<cfset companydsn = companydsn> 



<cfquery name="getMasterInfo" datasource="#companydsn#" maxrows="1">

	SELECT GUID, 
           PERSONNELIDNO, 
           LASTNAME,  
           FIRSTNAME, 
           POSITIONCODE, 
           DATEHIRED, 
           DEPARTMENTCODE, 
           SSSNUMBER, 
           SUPERIORCODE,
           LEVELCODE,
           COMPANYCODE,
           COSTCENTERCODE,
           GROUPCODE,
           DIVISIONCODE,
           SECTIONCODE,
           EMPLOYMENTSTATUS,
           MIDDLENAME
      FROM CMFPA
     WHERE PERSONNELIDNO = '#thepid#'

</cfquery>

<cfquery name="getImmediateSuperior" datasource="#companydsn#" maxrows="1">

    SELECT FIRSTNAME, LASTNAME
      FROM CMFPA
     WHERE PERSONNELIDNO = '#getMasterInfo.SUPERIORCODE#'
    ;

</cfquery>

<cfquery name="getmobilenum" datasource="#companydsn#" maxrows="1">

    SELECT BIRTHDAY, CONTACTCELLNUMBER, EMAILADDRESS, SEX, AGE, CIVILSTATUS, CONTACTADDRESS
      FROM CIN21PERSONALINFO
     WHERE PERSONNELIDNO = '#thepid#'
    ;

</cfquery>
    
    <cfset bday      = dateFormat(getmobilenum.BIRTHDAY, 'dd/mm/yyyy') />
    

	
	<cfif isdefined("getMasterInfo") >
		<cfoutput>
		{
		"success": "true",
		"data": 
					{
					   "xxxx": "debugging",
				<cfloop query="getMasterInfo">
					
						"lastname": #SerializeJSON(LASTNAME)#, 
						"firstname": #SerializeJSON(FIRSTNAME)#, 
						"middlename": #SerializeJSON(MIDDLENAME)#, 
						"position": #SerializeJSON(POSITIONCODE)#, 
						"level": #SerializeJSON(LEVELCODE)#, 
						"costcenter": #SerializeJSON(COSTCENTERCODE)#, 
						"brandorgroup": #SerializeJSON(GROUPCODE)#, 
						"division": #SerializeJSON(DIVISIONCODE)#, 
						"departmentorstore": #SerializeJSON(DEPARTMENTCODE)#, 
						"section": #SerializeJSON(SECTIONCODE)#, 
						"gender": #SerializeJSON(getmobilenum.SEX)#, 
						<cftry>
						"birthdate": #SerializeJSON(dateformat(getmobilenum.BIRTHDAY, 'YYYY-MM-DD'))#, 
						<cfcatch>
						"birthdate": "",	
						</cfcatch>
						</cftry>
						"age": #SerializeJSON(getmobilenum.AGE)#,  
						"civilstatus": #SerializeJSON(getmobilenum.CIVILSTATUS)#,  
						"address": #SerializeJSON(getmobilenum.CONTACTADDRESS)#,  
						"mobilenumber": #SerializeJSON(getmobilenum.CONTACTCELLNUMBER)#,  
						"email": #SerializeJSON(getmobilenum.EMAILADDRESS)#,  
						<cftry>
						"datehired": #SerializeJSON(dateformat(DATEHIRED, 'YYYY-MM-DD'))#, 
						<cfcatch>
						"datehired": ""
						</cfcatch> 
						</cftry>
						"employmentstatus": #SerializeJSON(EMPLOYMENTSTATUS)#,  
						"immediatesuperior": #SerializeJSON(getImmediateSuperior.FIRSTNAME & ' ' & getImmediateSuperior.LASTNAME)#
					
				
		        </cfloop>
		        }
		}
		</cfoutput>
	<cfelse>
		<cfoutput>
		{
		"success": "true",
		"data": {
						"lastname":  #SerializeJSON("empty")#, 
						"firstname": #SerializeJSON("empty")#
					}
		}
		</cfoutput>
	</cfif>
		<cfcatch>
			<cfoutput>
			   {
				"success": "true",
				"data": {
						"lastname":  "error", 
						"firstname": #SerializeJson(cfcatch.Detail & ' ' & cfcatch.Message)#
					}
				}
			</cfoutput>
			<cfreturn processkey >
		</cfcatch>
</cftry>
	
	<cfreturn processkey >
	
	
</cffunction>
	
	
	<cffunction
			name="saveexitmntrdata"
			returnformat="plain" 
			access="remote"
			returntype="string"
    >
    
    <cfargument name="token">
    
    <cfset controlnumber = trim(form.controlnumber) >
	
	
    <cfset eiObj = EntityLoad("ginexitinterviewform", {controlnumber = "#controlnumber#"}, true) >
	<cfset value = trim(form.value)>
	
	<cfset findres = findnocase("date", form.field) >
	<cfif findres GT 0 >
		<cfset value = left(value, 10)>
    </cfif>
	
	<cfset evaluate("eiObj.set#trim(form.field)#(value)") >
	
	<cfset EntitySave(eiObj) >
	
	<cfreturn
			token
	>
	
	</cffunction>
	
	<cffunction name="gridtoexcel"
				returntype="string"
				access="remote"
    >
    
    	<cfargument name = "token">
    
    	<cfquery name="mygriddata"
				 datasource="IBOSE_GLOBAL"
	    >
	    	SELECT * 
			  FROM GINEXITINTERVIEWFORM
		</cfquery>
   
   		<cfspreadsheet  
	    action="write" 
	    filename = "#expandpath('./')#ExitInterviewData.xls"
	    overwrite = "true"				  
	    query = "mygriddata" >
		
		<cfreturn token>
   
    </cffunction>
    
    


</cfcomponent>